package com.info.zhiduoduo.repository.mapper.entrustmentproject;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.entrustmentproject.EntrustmentProjectCompanyDto;
import com.info.zhiduoduo.repository.dto.entrustmentproject.EntrustmentProjectDto;
import com.info.zhiduoduo.repository.dto.entrustmentproject.GetListProjectDto;
import com.info.zhiduoduo.repository.entity.entrustmentproject.EntrustmentProject;

import org.apache.ibatis.annotations.*;

import java.util.Date;
import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author bob
 * @since 2021-07-10
 */
public interface EntrustmentProjectMapper extends BaseMapper<EntrustmentProject> {

	@Select(" select ep.*,c.compname as actualCompanyName,GROUP_CONCAT(epb.delivery_path) as deliveryPaths " +
			" from zdd_entrustment_project ep " +
			" left join zdd_company c on ep.actual_company_id = c.compid" +
			" left join zdd_entrustment_project_block epb on ep.id = epb.project_id" +
			" where CONCAT(ep.compname, ep.origin_file_name) like '%${name}%' " +
			" and if(isnull(#{projectType}) or #{projectType} = 0, 1=1, project_type = #{projectType})" +
			" and if(isnull(#{from}), 1=1, ep.createtime >= #{from})" +
			" and if(isnull(#{to}), 1=1, ep.createtime <= #{to})" +
			" and if(#{compid}=0, 1=1, ep.compid = #{compid})" +
			" GROUP BY ep.id " +
			" HAVING if(#{whetherUploadFile}=0, 1=1, " +
			"       if(#{whetherUploadFile} = 1,attachment_url is not null or deliveryPaths is not null," +
			"         attachment_url is null and deliveryPaths is null" +
			"        )" +
			"      ) " +
			" order by ep.state, ep.createtime desc ")
	List<EntrustmentProjectDto> listProjects(String name, Integer projectType, Date from, Date to,int compid,int whetherUploadFile);

	@Select(" select SUM(ep.imported_tax_inclusive_amount) as importedTaxInclusiveAmount, " +
			" SUM(ep.total_item_count) as totalItemCount, " +
			" SUM(ep.project_complete_count) as projectCompleteCount " +
			" from zdd_entrustment_project ep " +
			" where CONCAT(ep.compname, ep.origin_file_name) like '%${name}%' " +
			" and if(isnull(#{projectType}) or #{projectType} = 0, 1=1, project_type = #{projectType})" +
			" and if(isnull(#{from}), 1=1, ep.createtime >= #{from})" +
			" and if(isnull(#{to}), 1=1, ep.createtime <= #{to})" +
			" and if(#{compid}=0, 1=1, ep.compid = #{compid})")
	EntrustmentProjectDto importListStatistics(String name, Integer projectType, Date from, Date to,int compid);

	@Select(" select SUM(ep.imported_amount) AS importedAmount, " +
			" SUM(ep.deposit_total_amount) AS depositTotalAmount,  " +
			" SUM(ep.imported_tax_inclusive_amount)-SUM(ep.imported_amount) AS and1 " +
			" from zdd_entrustment_project ep " +
			" where CONCAT(ep.compname, ep.origin_file_name) like '%${name}%' " +
			" and if(isnull(#{projectType}) or #{projectType} = 0, 1=1, project_type = #{projectType})" +
			" and if(isnull(#{from}), 1=1, ep.createtime >= #{from})" +
			" and if(isnull(#{to}), 1=1, ep.createtime <= #{to})" +
			" and if(#{compid}=0, 1=1, ep.compid = #{compid}) " )
	GetListProjectDto getListProjects(String name, Integer projectType, Date from, Date to, int compid);

	@Select(" select COUNT(ep.id) " +
			" from zdd_entrustment_project ep  " +
			" where ep.state in(3,4) " +
			" and if(isnull(#{projectType}) or #{projectType} = 0, 1=1, project_type = #{projectType})")
	int getCount(Integer projectType);

}
